﻿Imports System.Data.SqlClient
Imports System.IO
Imports GrapeCity.Forguncy.SecurityProvider
Imports GrapeCity.Forguncy.ServerApi

Public Class LQPasswordSecurityProvider
    Implements ISecurityProvider
    Implements ISupportSettings

    ''' <summary>
    ''' '数据库连接
    ''' </summary>
    Private vConnection As String
    Private Const tableDeptinfo As String = "dt_deptinfo"
    Private Const tableUserInfo As String = "dt_userinfo"
 '   private strlogsqlcmd as string
    Public ReadOnly Property UserInformations As UserInformations Implements ISecurityProvider.UserInformations
        Get
            vConnection = String.Format("Server={0};Database={1};User Id={2};Password={3}", "191.168.77.101", "KeyroadWMS", "sa", "WMS@123456")
         '   strlogsqlcmd=$"insert into s_log(billdate,billtype,operator,mod,opcontent,sqlcmd,status) select getdate() as billdate,'99','admin','test','{vConnection}','','1'"
          
            Dim userInfo = New UserInformations()

            Dim con As SqlConnection = New SqlConnection(vConnection)
            If con.State <> ConnectionState.Open Then
                con.Open()
            End If

            Dim userTable = GetUser(con, userInfo)
            '递交角色 2020.2.4
            GetRoles(userInfo, userTable, con)
           GetDepartment(con, userInfo, userTable)
            con.Close()
            Return userInfo
        End Get
    End Property

    Public ReadOnly Property Name As String Implements ISecurityProvider.Name
        Get
            Return "LQPasswordSecurityProvider"
        End Get
    End Property

    Public ReadOnly Property AuthenticationType As AuthenticationType Implements ISecurityProvider.AuthenticationType
        Get
            Return AuthenticationType.UserNameAndPassword
        End Get
    End Property

    Public ReadOnly Property UserInformationStorageMode As UserInformationStorageMode Implements ISecurityProvider.UserInformationStorageMode
        Get
            Return UserInformationStorageMode.InMemoryCache
        End Get
    End Property

    Public ReadOnly Property AllowLogout As Boolean Implements ISecurityProvider.AllowLogout
        Get
            Return True
        End Get
    End Property

    Public ReadOnly Property Settings As List(Of SecurityProviderSettings) Implements ISupportSettings.Settings
        Get
            Return New List(Of SecurityProviderSettings)()
        End Get
    End Property

    Public Function VerifyUser(properties As Dictionary(Of String, String)) As User Implements ISecurityProvider.VerifyUser

        Dim userName = properties("userName")
        Dim password = properties("password")

        Dim con As SqlConnection = New SqlConnection(vConnection)
        If con.State <> ConnectionState.Open Then
            con.Open()
        End If

        Dim strsqlcmd As String = String.Format("select * from m_userinfo where username = '{0}' or userid='{0}' ", userName)
        Dim dt As New DataTable
        dt = F_GetDataTable(strsqlcmd, CommandType.Text, con)

        Try
            Dim struserid As String = Convert.ToString(dt.Rows(0)("userid"))
            Dim strpassword As String = Convert.ToString(dt.Rows(0)("NetPassword"))
            If userName IsNot Nothing AndAlso password IsNot Nothing Then
                Dim strjmpassword As String = Entry.Encrypt(password, "", "")

                If strpassword = strjmpassword Then
                    Return New User With {
                        .UserId = struserid,
                        .FullName = userName
                        }
                End If
            End If

            Return Nothing
        Catch ex As Exception
            Throw
        Finally
            con.Close()
        End Try
    End Function
    ''' <summary>
    ''' 2019.12.8
    ''' 加入用户信息
    ''' </summary>
    ''' <param name="vSqlcon"></param>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    Private Function GetUser(ByVal vsqlCon As SqlConnection, ByVal userInfo As UserInformations) As Dictionary(Of Integer, User)
        Dim strsqlcmd As String = String.Format("select  row_number() over(order BY UserID ) AS id, userid,username,email,post,isnull(company,'') as company,isnull(deptname,'') as deptname,isnull(mobile,'') as mobile,isnull(tel,'') as tel,isnull(deptid,'') as deptid from m_userinfo where sysid='lq' and Status='1'  order by userid  ")
        Dim dt As New DataTable
        dt = F_GetDataTable(strsqlcmd, CommandType.Text, vsqlCon)
        Dim strPath As String = "d:\log"
        Dim vFileName As String = strPath + "\" & Now.ToString("yyyyMMdd") & ".txt"
        Try

            Dim i As Integer = 1
            Dim userDic As Dictionary(Of Integer, User) = New Dictionary(Of Integer, User)()
            For Each dr As DataRow In dt.Rows
                Dim strUserid As String = Convert.ToString(dr("UserID"))
                Dim strusername As String = Convert.ToString(dr("UserName"))
                Dim stremail As String = Convert.ToString(dr("Email"))
                Dim strpost As String = Convert.ToString(dr("post"))
                Dim strcompany As String = Convert.ToString(dr("company"))
                Dim strdeptname As String = Convert.ToString(dr("deptname"))
                Dim strtel As String = Convert.ToString(dr("tel"))
                Dim strmobile As String = Convert.ToString(dr("mobile"))
                Dim user As New User() With {
                        .UserId = strUserid,
                        .Email = stremail,
                        .FullName = strusername
                        }
                user.Properties.Add("手机", strmobile)
                user.Properties.Add("电话", strtel)
                user.Properties.Add("生日", "1900-01-01")
                user.Properties.Add("国籍", "中国")
                user.Properties.Add("岗位", strpost)
                user.Properties.Add("所属公司", strcompany)
                user.Properties.Add("所属部门", strdeptname)
                userInfo.Users.Add(user)
                userDic.Add(i, user)
                i = i + 1
            Next


            Return userDic
        Catch ex As Exception
            Loglib.WriteLog("日志记录", "身份验证", ex.Message)
        Finally
        End Try
    End Function

    ''' <summary>
    ''' 2020.2.4
    ''' 添加角色信息
    ''' </summary>
    ''' <param name="vUserInfo"></param>
    ''' <param name="userDic"></param>
    ''' <param name="vConnection"></param>
    Private Sub GetRoles(ByVal vUserInfo As UserInformations, ByVal userDic As Dictionary(Of Integer, User), ByVal vConnection As SqlConnection)
        Try
            Dim strsqlcmd As String = "select distinct isnull(post,'') as post from m_userinfo where Status='1' and isnull(post,'')<>''  "
            Dim dtuser As DataTable
            Dim strusersqlcmd As String = "select row_number() over(order BY UserID ) AS seqno,  userid,post  from m_userinfo where sysid='lq' and Status='1' order by userid  "
            dtuser = GetDataTable(strusersqlcmd, vConnection)
            Dim Roledt As New DataTable
            Roledt = GetDataTable(strsqlcmd, vConnection)
            '//添加角色
            Dim i As Integer = 0
            For Each dr As DataRow In Roledt.Rows
                Dim strrole As String = Convert.ToString(dr("post").ToString())
                Dim role = New Role() With {
                        .Name = dr("post").ToString()
                                            }
                Dim query = From dta In dtuser
                            Where dta!Post.ToString.Equals(strrole)
                            Select dta
                If query.Count() > 0 Then
                    Dim dtquery As New DataTable
                    dtquery = query.CopyToDataTable()
                    For Each druser As DataRow In dtquery.Rows
                        Dim intseqno = CInt(druser("seqno"))
                        If userDic.ContainsKey(intseqno) Then
                            role.Users.Add(userDic(intseqno))
                        End If
                    Next
                End If

                vUserInfo.Roles.Add(role)
                i = i + 1
            Next

        Catch ex As Exception
            Loglib.WriteLog("d:\", "log", "导入角色", "第三方", ex.Message)
        End Try
    End Sub

    Public Overloads Function F_GetDataTable(ByVal vCommandText As String, ByVal vcommandType As CommandType, ByVal vSqlconn As SqlConnection) As DataTable
        Dim dt As New DataTable
        Try
            Dim vSqlcmd As SqlCommand = New SqlCommand()
            With vSqlcmd
                '//设置sqlcommand对应数据库连接
                .Connection = vSqlconn
                .CommandText = vCommandText '//操作SQL命令
                .CommandType = vcommandType '//命令操作类型
            End With
            Dim da As SqlDataAdapter
            da = New SqlDataAdapter(vSqlcmd)
            da.Fill(dt)
        Catch ex As Exception

        End Try
        Return dt
    End Function



    ''' <summary>
    ''' 时间:2024.1.21
    ''' 获取组织结构信息
    ''' </summary>
    ''' <param name="con"></param>
    ''' <param name="userInfo"></param>
    ''' <param name="userDic"></param>
    Private Sub GetDepartment(con As SqlConnection, userInfo As UserInformations, userDic As Dictionary(Of Integer, User))
        Dim strdeptinfosqlcmd As String = $"select id, deptname, parentid  from {tableDeptinfo} "
        Dim departmentTable = GetDataTable(strdeptinfosqlcmd, con)
        Dim strUserInfosqlcmd As String = $"select  row_number() over(order BY m.UserID ) AS id,m.userid , isnull(m.deptid,-1) as deptid , cast(isnull(d.leader ,0) as INT) AS leader,isnull(d.post,'') as post from m_userinfo m LEFT OUTER JOIN {tableUserInfo} d ON m.DingTalkID=d.userid and m.deptid=d.deptid where Status='1'  and m.sysid='johnshen' order by m.userid "
        Dim userTable = GetDataTable(strUserInfosqlcmd, con)
        Dim departMemberList = GetList(userTable)
        Dim orgList As New List(Of OrganizationInfo)()
        Try
            For Each dr As DataRow In departmentTable.Rows
                Dim intid As String = Convert.ToInt32(dr("id"))
                Dim intparentid As Integer?
                Try
                    intparentid = Convert.ToInt32(dr("parentid"))
                Catch ex As Exception
                    intparentid = Nothing
                End Try
                Dim strDeptname As String = Convert.ToString(dr("deptname"))
                Dim org As New OrganizationInfo() With {
                                            .id = intid,
                                           .ParentId = intparentid,
                                       .Node = New Organization() With {.Name = strDeptname}}
                orgList.Add(org)
                '    userInfo.Organizations.Add(org.Node)
            Next

            '添加部门信息
            For Each node In orgList
                If node.ParentId <= 1 Then
                    '默认第一级组织部门加入
                    userInfo.Organizations.Add(node.Node)
                    '第二级部门加入
                    Dim query = From dta In orgList
                                Where dta.ParentId = node.id And dta.ParentId > 1
                                Select dta
                    If query.Count() > 0 Then
                        For Each subnode In query
                            node.Node.SubOrganizations.Add(subnode.Node) '加入子部门
                        Next
                        '    node.Node.SubOrganizations.AddRange()
                    End If
                End If
                '添加人员
                Dim querymem = From dta In departMemberList
                               Where dta!deptid = node.id
                               Select dta
                If querymem.Count() > 0 Then
                    For Each mem In querymem
                        Dim intid As String = Convert.ToInt32(mem("id"))
                        Dim blnleader As Boolean = Convert.ToBoolean(mem("leader"))
                        Dim strpost As String = Convert.ToString(mem("post")) '岗位
                        If userDic.ContainsKey(intid) Then
                            node.Node.Members.Add(New GrapeCity.Forguncy.SecurityProvider.OrganizationMember() With {
.IsLeader = blnleader,
.User = userDic(intid)})
                        End If
                    Next
                End If
            Next
        Catch e As Exception
            Throw
        Finally
        End Try
    End Sub
    Private Function AddSubOrganization(parentId As Integer, allNodes As List(Of OrganizationInfo)) As List(Of Organization)
        Dim result As New List(Of Organization)()

        For Each node In allNodes
            If node.ParentId = parentId Then
                result.Add(node.Node)
                node.Node.SubOrganizations.AddRange(AddSubOrganization(node.id, allNodes))
            End If
        Next

        Return result
    End Function


    ''' <summary>
    ''' 说明：执行查询SQL语句，返回记录集内存库表DataTable   
    ''' </summary>
    ''' <param name="vcommandText">数据库链接字符串</param>
    ''' <param name="vcommandType">命令类型</param>
    ''' <param name="vcommandParameters">SQL语句</param>
    ''' <param name="vConnection">数据库链接字符串</param>
    ''' <returns>返回内存库表DataTable</returns>
    ''' <remarks></remarks>
    Public Overloads Function F_GetDataTable(
                                             ByVal vcommandText As String,
                                             ByVal vcommandType As CommandType,
                                             ByVal vcommandParameters As SqlParameter(), ByVal vConnection As String) As DataTable
        Dim dt As New DataTable

        '//数据库链接字符串
        Dim cn As New SqlConnection(vConnection)
        Dim ds As New DataSet
        Try
            If cn.State <> ConnectionState.Open Then
                cn.Open()
            End If
            Dim vsqlcmd As New SqlCommand
            With vsqlcmd
                '//设置sqlcommand对应数据库连接
                .Connection = cn
                .CommandText = vcommandText '//操作SQL命令
                .CommandType = vcommandType '//命令操作类型
                '//如果存在参数数组，则添加到sqlcommand
                If Not (vcommandParameters Is Nothing) Then
                    ' AttachParameters(vSqlcmd, vcommandParameters)
                    Dim p As SqlParameter
                    For Each p In vcommandParameters
                        '//参数可输出也 可输入

                        If p.Direction = ParameterDirection.InputOutput And p.Value Is Nothing Then
                            p.Value = Nothing
                        End If
                        '对于存储过程，有些参数是输出 
                        If p.Direction = ParameterDirection.Output Then
                            '//sqlcommand添加参数变量
                            vsqlcmd.Parameters.Add(p).Direction = ParameterDirection.Output
                        Else
                            '//sqlcommand添加参数变量
                            vsqlcmd.Parameters.Add(p)
                        End If

                    Next
                End If
            End With
            Dim da As SqlDataAdapter
            da = New SqlDataAdapter(vsqlcmd)
            da.Fill(ds)
            '//清理sqlpara
            vsqlcmd.Parameters.Clear()
            '关闭连接池 
            If (ConnectionState.Closed <> cn.State) Then
                cn.Close()
                cn = Nothing '//销毁连接
            End If
            dt = ds.Tables(0)
            Return dt
        Finally
            cn.Dispose()
        End Try
    End Function
    Private Function GetList(ByVal dataTable As DataTable) As List(Of DataRow)
        Dim list As List(Of DataRow) = New List(Of DataRow)()

        For i As Integer = 0 To dataTable.Rows.Count - 1
            list.Add(dataTable.Rows(i))
        Next

        Return list
    End Function '
    Private Function GetDataTable(ByVal sql As String, ByVal con As SqlConnection) As DataTable
        Using com As SqlCommand = New SqlCommand(sql, con)

            Using dataAdapter = New SqlDataAdapter(com)
                Dim dataTable = New DataTable()
                dataAdapter.Fill(dataTable)
                Return dataTable
            End Using
        End Using
    End Function
    Public Sub UpdateSetting(dictionary As Dictionary(Of String, Object)) Implements ISupportSettings.UpdateSetting
     '   Throw New NotImplementedException()
    End Sub

End Class
''' <summary>
''' 2024.1.21
''' 组织结构信息
''' </summary>
Public Class OrganizationInfo
    ''' <summary>
    ''' 组织id
    ''' </summary>
    ''' <returns></returns>
    Public Property id As Integer
    ''' <summary>
    ''' 组织结构父级id
    ''' 带?表明该属性可空，空为nothing
    ''' </summary>
    ''' <returns></returns>
    Public Property ParentId As Integer?
    ''' <summary>
    ''' 组织节点信息
    ''' </summary>
    ''' <returns></returns>
    Public Property Node As Organization
End Class


